"""
openpyxl 与 pandas 相互转化
"""
from openpyxl import Workbook, load_workbook
import pandas as pd
from openpyxl.utils.dataframe import dataframe_to_rows

"""
pandas -> openpyxl
"""
df = pd.DataFrame(
    [[1, 2, 3],
     [4, 5, 6],
     [7, 8, 9]],
    columns=list('ABC'),
    index=list('XYZ')
)
# print(df)
# 是否需要 index header
rows = dataframe_to_rows(df, index=False, header=True)
# <generator object dataframe_to_rows at 0x000001DB13396E58>
# print(rows)

wb = Workbook()
ws = wb.active

for row in rows:
    ws.append(row)

wb.save('1.xlsx')

"""
openpyxl -> pandas
"""
wb = load_workbook('tips.xlsx')
ws = wb.active

data = list(ws.values)
# print(data)
# 第 0 行作为列名；从第2列作为数据进行切片
df = pd.DataFrame(data[1:], columns=data[0])
#    total_bill   tip     sex smoker  day    time  size
# 0       16.99  1.01  Female     No  Sun  Dinner     2
# 1       10.34  1.66    Male     No  Sun  Dinner     3
# 2       21.01  3.50    Male     No  Sun  Dinner     3
# 3       23.68  3.31    Male     No  Sun  Dinner     2
# 4       24.59  3.61  Female     No  Sun  Dinner     4
print(df.head())
